#region (c) 2014 THN Solutions LLC. - All rights reserved
/*
THN DATA ABSTRACTION LAYER version 1

Copyright (c) 2014, THN Solutions LLC. ( www.ThnSolutions.com )
Author: Nguyễn, M. Hải                 ( www.Minh-Hai.com     )
All rights reserved.

This library is dual-licensed.
  + For commercial software, please obtain a commercial license from THN Solutions LLC.

  + For free software, this library is licensed under GPL version 3. A summary of GPLv3 is
    listed below. You should also find a copy of GPLv3 as file License_GPLv3.txt included
	with the source-code files.

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License version 3 as published by
    the Free Software Foundation  of the License.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/
#endregion

#region Using directives
using System;
using System.Data;
using System.Text;
using System.Collections;
using Thn.Application;
using Thn.Application.Language;
using Thn.Collections;
using Thn.Data;
using Thn.Data.Query;
using Thn.Data.Settings;
using Thn.Data.Mapping;
using Thn.Data.Exceptions;
using Thn.Data.Database.Sql.Dialect;
#endregion

namespace Thn.Data.Database.Sql
{
    /// <summary>
    /// Abstract base class for all databases that operates based on SQL.
    /// Transactions supported by database engine is presumed to be single-level. (Multiple level transactions will be simulated by this database class)
    /// </summary>
    public abstract class AbstractSqlDatabase: AbstractDatabase
    {        
        /// <summary>
        /// String constants
        /// </summary>
        SqlDialectStrings sql = null;

        /// <summary>
        /// When true, exceptions will not be thrown regardless of DataTierSettings
        /// </summary>
        protected bool mSuppressException = false;

        /// <summary>
        /// A dummy object for thread-locking
        /// </summary>
        protected static volatile object mSyncRoot = new object();

        #region Debug
        void LogCommand(IDbCommand command)
        {
            if ((command.Parameters != null) && (command.Parameters.Count > 0))
            {
                object logScope = Log.Start(command.CommandText);

                //Log.Debug(command.CommandText);
                if (command.Parameters != null)
                {
                    foreach (IDataParameter parameter in command.Parameters)
                    {
                        if (parameter.Value == null) Log.Debug(string.Format("         Param: {0} - Value: {1} - Type: {2} - Direction: {3}", parameter.ParameterName, "[null]", parameter.DbType, parameter.Direction));
                        else if ((parameter.Value is string) && (((string)parameter.Value).Equals(string.Empty))) Log.Debug(string.Format("         Param: {0} - Value: {1} - Type: {2} - Direction: {3}", parameter.ParameterName, "[empty]", parameter.DbType, parameter.Direction));
                        else Log.Debug(string.Format("         Param: {0} - Value: {1} - Type: {2} - Direction: {3}", parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction));
                    }
                }

                Log.End(logScope);
            }
            else Log.Debug(command.CommandText);
        }
        #endregion

        #region Dialect
        private ISqlDialect mDialect;
        /// <summary>
        /// Gets/Sets dialect used by this database
        /// </summary>
        public ISqlDialect Dialect
        {
            get { return mDialect; }
            set 
            {
                mDialect = value;
                if (mDialect != null) sql = mDialect.SqlStrings;
            }
        }
        #endregion
		
        #region I/O
        #region Connection handling

        #region Connection string
        private string mConnectionString = "";
        /// <summary>
        /// Gets/Sets the connection string
        /// </summary>
        protected string ConnectionString
        { 
            get { return mConnectionString; }
            set { mConnectionString = value; }
        }
        #endregion

        #region Connection
        private IDbConnection mConnection = null;
        /// <summary>
        /// Gets connection object
        /// </summary>
        protected IDbConnection Connection
        {
            get
            {
                if (mConnection == null) mConnection = mDialect.CreateConnection();
                return mConnection;
            }
        }
        #endregion

        #region Always close connection
        private bool mAlwaysCloseConnection = false;
        /// <summary>
        /// Automatically close connection after each database access.
        /// Default is false.
        /// </summary>
        public bool AlwaysCloseConnection
        {
            get
            {
                return mAlwaysCloseConnection;
            }

            set
            {
                mAlwaysCloseConnection = value;
            }
        }
        #endregion

        #region Is connection ready
        /// <summary>
        /// Checks whether connection to database is open
        /// </summary>
        protected bool IsConnectionReady
        {
            get
            {
                return (mConnection != null) && (mConnection.State == ConnectionState.Open);
            }
        }
        #endregion

        #region Prepare connection
        /// <summary>
        /// Do neccessary stuff to ensure connection to database is alive
        /// All database access activities should use PrepareConnection and CleanupConnection.
        /// </summary>
        protected virtual void PrepareConnection()
        {
            if (!IsConnectionReady)
            {
                try
                {
                    if (mConnection == null) mConnection = mDialect.CreateConnection();
                    if (mConnection != null)
                    {
                        mConnection.ConnectionString = mConnectionString;
                        mConnection.Open();
                    }
                }
                catch (Exception error)
                {
                    if (!mSuppressException)
                    {
                        DataExceptionBase exception = mDialect.ParseException(error);

                        if (DataTierSettings.AutoPublishError) Log.Error(exception);
                        if (!DataTierSettings.SilentError) throw exception;
                    }
                }
            }
        }
        #endregion

        #region Cleanup connection
        /// <summary>
        /// Free connection if needed
        /// </summary>
        protected virtual void CleanupConnection()
        {
            if ((mAlwaysCloseConnection) && (IsConnectionReady) && (mCurrentTransaction == null))
            {
                mConnection.Close();
            }
        }
        #endregion
        #endregion        

        #region Initialize
        /// <summary>
        /// Prepare connection to database
        /// </summary>
        /// <param name="parameters">connection string</param>
        public override void Initialize(string parameters)
        {
            if (IsConnectionReady)
            {//allready connected => disconnect then reconnect
                Connection.Close();
                mConnection = null;
            }

            ConnectionString = parameters;            
            PrepareConnection();
            bool success = IsConnectionReady;
            CleanupConnection();
        }
        #endregion

        #region Close
        /// <summary>
        /// Close connection to the underlying database, if neccessary
        /// </summary>
        public override void Close()
        {
            if (IsConnectionReady)
            {//allready connected => disconnect then reconnect
                Connection.Close();
                mConnection = null;
            }
        }
        #endregion
        #endregion

        #region Thread-safe
        private bool mThreadSafe = true;
        /// <summary>
        /// Locking and synchronization between threads
        /// <para>Default is true</para>
        /// </summary>
        public bool ThreadSafe
        {
            get { return mThreadSafe; }
            set { mThreadSafe = value; }
        }
        #endregion

        #region Query Execution
        #region Execute Non Query
        /// <summary>
        /// Execute a query that returns no data.
        /// </summary>
        /// <param name="sqlCommand">the sql command to execute</param>
        /// <param name="disposeCommand">automaticaly dispose command object after executing</param>
        /// <returns>Number of records have been affected (if available)</returns>
        protected virtual int ExecuteNonQuery(string sqlCommand, bool disposeCommand)
        {
            IDbCommand cmd = mDialect.CreateCommand();
            cmd.CommandText = sqlCommand;
            return ExecuteNonQuery(cmd, disposeCommand);
        }

        /// <summary>
        /// Execute a query that returns no data.
        /// </summary>
        /// <param name="command">the data command to execute</param>
        /// <param name="disposeCommand">automaticaly dispose command object after executing</param>
        /// <returns>Number of records have been affected (if available)</returns>
        protected virtual int ExecuteNonQuery(IDbCommand command, bool disposeCommand)
        {
            int result = 0;

            if (mThreadSafe)
            {
                Exception ex = null;

                lock(mSyncRoot)
                {
                    //catch exception so that other thread won't be stuck while
                    //this thread crashes
                    try
                    {
                        result = DoExecuteNonQuery(command, disposeCommand);
                    }
                    catch (Exception e)
                    {
                        ex = e;
                    }
                }

                //rethrow exception if available
                if (ex != null) throw ex;
            }
            else result = DoExecuteNonQuery(command, disposeCommand);

            return result;
        }

        /// <summary>
        /// The actual method that execute a query and return no data
        /// </summary>
        /// <param name="command">the data command to execute</param>
        /// <param name="disposeCommand">automaticaly dispose command object after executing</param>
        /// <returns>Number of records have been affected (if available)</returns>
        int DoExecuteNonQuery(IDbCommand command, bool disposeCommand)
        {
            int affectedRows = 0;

            PrepareConnection();
            command.Connection = mConnection;
            if (mCurrentTransaction != null) command.Transaction = mCurrentTransaction;

//#if (DEBUG)
            if (DataTierSettings.LogCommand) LogCommand(command);
//#endif

            try
            {
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                if (!mSuppressException)
                {
                    DataExceptionBase exception = mDialect.ParseException(error);

                    if (DataTierSettings.AutoPublishError)
                    {
                        if (DataTierSettings.LogCommand) LogCommand(command);
                        else Log.Error(exception);
                    }
                    if (!(DataTierSettings.SilentError)) throw exception;
                }
            }
            finally
            {
                if ((command != null) && (disposeCommand)) command.Dispose();
            }

            CleanupConnection();

            return affectedRows;
        }        
        #endregion

        #region Execute Scalar
        /// <summary>
        /// Execute a query that returns only one output
        /// </summary>
        /// <param name="sqlCommand">the sql command to execute</param>
        /// <param name="disposeCommand">automaticaly dispose command object after executing</param>
        protected virtual object ExecuteScalar(string sqlCommand, bool disposeCommand)
        {
            IDbCommand cmd = mDialect.CreateCommand();
            cmd.CommandText = sqlCommand;
            return ExecuteScalar(cmd, disposeCommand);
        }

        /// <summary>
        /// Execute a query that returns only one output
        /// </summary>
        /// <param name="command">the data command to execute</param>
        /// <param name="disposeCommand">automaticaly dispose command object after executing</param>
        protected virtual object ExecuteScalar(IDbCommand command, bool disposeCommand)
        {
            object result = 0;

            if (mThreadSafe)
            {
                Exception ex = null;

                lock (mSyncRoot)
                {
                    //catch exception so that other thread won't be stuck while
                    //this thread crashes
                    try
                    {
                        result = DoExecuteScalar(command, disposeCommand);
                    }
                    catch (Exception e)
                    {
                        ex = e;
                    }
                }

                //rethrow exception if available
                if (ex != null) throw ex;
            }
            else result = DoExecuteScalar(command, disposeCommand);

            return result;
        }

        /// <summary>
        /// The actual method that execute a query and return only one output
        /// </summary>
        /// <param name="command">the data command to execute</param>
        /// <param name="disposeCommand">automaticaly dispose command object after executing</param>
        object DoExecuteScalar(IDbCommand command, bool disposeCommand)
        {
            object result = null;

            PrepareConnection();
            command.Connection = mConnection;
            if (mCurrentTransaction != null) command.Transaction = mCurrentTransaction;

            //#if (DEBUG)
            if (DataTierSettings.LogCommand) LogCommand(command);
            //#endif

            try
            {
                result = command.ExecuteScalar();
            }
            catch (Exception error)
            {
                if (!mSuppressException)
                {
                    DataExceptionBase exception = mDialect.ParseException(error);

                    if (DataTierSettings.AutoPublishError)
                    {
                        if (DataTierSettings.LogCommand) LogCommand(command);
                        else Log.Error(exception);
                    }
                    if (!(DataTierSettings.SilentError)) throw exception;
                }
            }
            finally
            {
                if ((command != null) && (disposeCommand)) command.Dispose();
            }

            CleanupConnection();

            return result;
        }
        #endregion

        #region Execute Reader One Entity
        /// <summary>
        /// Execute reader command for one entity type only.
        /// </summary>
        /// <param name="entity">mapping of object entity</param>
        /// <param name="attributes">attributes which will be read</param>
        /// <param name="sqlCommand">sql command to execute</param>
        /// <param name="disposeCommand">dispose the command object after finish reading</param>
        /// <returns>Always return a collection. Length is zero if no records found</returns>
        protected IList ExecuteReaderOneEntity(ObjectMap entity, AttributeMap[] attributes, string sqlCommand, bool disposeCommand)
        {
            IDbCommand cmd = mDialect.CreateCommand();
            cmd.CommandText = sqlCommand;
            return ExecuteReaderOneEntity(entity, attributes, cmd, disposeCommand);
        }

        /// <summary>
        /// Execute reader command for one entity type only.
        /// </summary>
        /// <param name="entity">mapping of object entity</param>
        /// <param name="attributes">attributes which will be read</param>
        /// <param name="command">data command to execute</param>
        /// <param name="disposeCommand">dispose the command object after finish reading</param>
        /// <returns>Always return a collection. Length is zero if no records found</returns>
        protected IList ExecuteReaderOneEntity(ObjectMap entity, AttributeMap[] attributes, IDbCommand command, bool disposeCommand)
        {
            IList result = null;

            if (mThreadSafe)
            {
                Exception ex = null;

                lock (mSyncRoot)
                {
                    //catch exception so that other thread won't be stuck while
                    //this thread crashes
                    try
                    {
                        result = DoExecuteReaderOneEntity(entity, attributes, command, disposeCommand);
                    }
                    catch (Exception e)
                    {
                        ex = e;
                    }
                }

                //rethrow exception if available
                if (ex != null) throw ex;
            }
            else result = DoExecuteReaderOneEntity(entity, attributes, command, disposeCommand);

            return result;
        }

        /// <summary>
        /// The actual method that execute reader command for one entity type only
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="attributes"></param>
        /// <param name="command"></param>
        /// <param name="disposeCommand"></param>
        /// <returns></returns>
        IList DoExecuteReaderOneEntity(ObjectMap entity, AttributeMap[] attributes, IDbCommand command, bool disposeCommand)
        {
            IList result = null;
            Thn.Collections.ArrayList lstData = new Thn.Collections.ArrayList();
            lstData.ItemType = entity.ObjectEntity;
            result = lstData;

            PrepareConnection();
            command.Connection = this.Connection;
            IDataReader reader = null;

//#if (DEBUG)
            if (DataTierSettings.LogCommand) LogCommand(command);
//#endif

            try
            {
                reader = command.ExecuteReader();

                if (reader != null)
                {
                    while (reader.Read())
                    {
                        //construct new instance
                        object obj = Activator.CreateInstance(entity.ObjectEntity);

                        //assign values from database
                        for (int i = 0; i < attributes.Length; i++)
                        {
                            object value = reader.GetValue(i);
                            if ((value != null) && (value != DBNull.Value))
                            {
                                mMapper.SetObjectAttributeValue(obj, attributes[i], value);
                            }
                        }

                        //put to result list
                        result.Add(obj);
                    }
                }//reader != null
            }//execute reader
            catch (Exception error)
            {
                if (!mSuppressException)
                {
                    DataExceptionBase exception = mDialect.ParseException(error);

                    if (DataTierSettings.AutoPublishError)
                    {
                        if (DataTierSettings.LogCommand) LogCommand(command);
                        else Log.Error(exception);
                    }
                    if (!(DataTierSettings.SilentError)) throw exception;
                }
            }
            finally
            {
                if (reader != null) reader.Close();
                if ((command != null) && (disposeCommand)) command.Dispose();
            }

            CleanupConnection();

            return result;
        }
        #endregion
        #endregion

        #region Builders
        #region Build Column Names
        /// <summary>
        /// Appends attributes as column names to buffer. Does not include column type or group start, end.
        /// </summary>
        /// <param name="attributes">Attribute mappings</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="prefix">text to append before each column</param>
        protected virtual void BuildColumnNames(AttributeMap[] attributes, StringBuilder buffer, string prefix)
        {
            BuildColumnNames(attributes, buffer, prefix, true);
        }
        /// <summary>
        /// Appends attributes as column names to buffer. Does not include column type or group start, end.
        /// </summary>
        /// <param name="attributes">Attribute mappings</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="prefix">text to append before each column</param>
        /// <param name="includeAutoGenerated">Include attribute map which is auto-generated</param>
        protected virtual void BuildColumnNames(AttributeMap[] attributes, StringBuilder buffer, string prefix, bool includeAutoGenerated)
        {
            if (includeAutoGenerated)
            {
                for (int i = 0; i < attributes.Length; i++)
                {
                    if (i > 0) buffer.Append(sql.ItemSeparator);
                    buffer.Append(prefix + sql.ItemStart + attributes[i].DataAttribute + sql.ItemEnd);
                }
            }
            else
            {
                int counter = 0;
                for (int i = 0; i < attributes.Length; i++)
                {
                    AttributeMap map = attributes[i];
                    if (!map.IsAutoGenerated)
                    {
                        if (counter > 0) buffer.Append(sql.ItemSeparator);
                        buffer.Append(prefix + sql.ItemStart + map.DataAttribute + sql.ItemEnd);
                        counter++;
                    }
                }
            }
        }
        #endregion

        #region Build Column Names with Column Types
        /// <summary>
        /// Appends attributes as column names to buffer. Does not include column type or group start, end.
        /// </summary>
        /// <param name="attributes">Attribute mappings</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="prefix">text to append before each column</param>
        protected virtual void BuildColumnNamesWithTypes(AttributeMap[] attributes, StringBuilder buffer, string prefix)
        {
            AttributeMap map = null;
            for (int i = 0; i < attributes.Length; i++)
            {
                map = attributes[i];
                
                //append column name                
                if (i > 0) buffer.Append(sql.ItemSeparator);                
                buffer.Append(prefix + sql.ItemStart + attributes[i].DataAttribute + sql.ItemEnd + " ");

                //append type                
                if (map.DataAttributeTypeSpecified) buffer.Append(map.DataAttributeType.ToString());
                else buffer.Append(mDialect.GetDataAttributeAsString(map));

                //append size
                if (map.Size > 0)
                {
                    buffer.Append(sql.GroupStart + map.Size.ToString() + sql.GroupEnd);
                }

                //append required
                if (map.IsRequired) buffer.Append(sql.Required);                

                //append Identity
                if (map.IsAutoGenerated) buffer.Append(sql.Identity);
            }
        }
        #endregion

        #region Build Condition
        #region Build Condition
        /// <summary>
        /// Appends condition to buffer. Add values to an array while parsing condition
        /// </summary>
        /// <param name="con">Condition model</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="valueStack">value list to add condition values while parsing condition</param>
        protected virtual void BuildCondition(Condition con, StringBuilder buffer, Thn.Collections.ArrayList valueStack)
        {
            //" WHERE xxx"
            buffer.Append(sql.Condition + ParseCondition(con, valueStack));
        }
        #endregion

        #region Parse Condition
        /// <summary>
        /// Convert a given condition to string
        /// </summary>
        protected string ParseCondition(Condition con, Thn.Collections.ArrayList valueStack)
        {
            string result = "";
            string prefix = sql.ParameterStart + "con";
            if (con is NotCondition)
            {
                NotCondition notCon = (NotCondition)con;
                //"NOT xxx"
                result = sql.Not + ParseCondition(notCon.Inner, valueStack);
            }
            else if (con is NestedCondition)
            {//Nested condition
                NestedCondition nc = (NestedCondition)con;

                string left = ParseCondition(nc.Left, valueStack);
                string right = ParseCondition(nc.Right, valueStack);

                if (nc.Combination == CombinationType.And)
                {
                    //"(xxx AND xxx)"
                    result = sql.GroupStart + left + sql.And + right + sql.GroupEnd;
                }
                else if (nc.Combination == CombinationType.Or)
                {
                    //"(xxx OR xxx)"
                    result = sql.GroupStart + left + sql.Or + right + sql.GroupEnd;
                }
            }//Nested condition
            else
            {//Single condition
                SingleCondition sc = (SingleCondition)con;
                switch (sc.Operation)
                {
                    case ComparisionType.Equal:
                        //"(field = @conxxx)"
                        result = sc.Field + sql.Equal + prefix + valueStack.Count.ToString();
                        break;

                    case ComparisionType.NotEqual:
                        //"(field <> @conxxx)"
                        result = sc.Field + sql.NotEqual + prefix + valueStack.Count.ToString();
                        break;

                    case ComparisionType.Less:
                        //"(field < @conxxx)"
                        result = sc.Field + sql.Less + prefix + valueStack.Count.ToString();
                        break;

                    case ComparisionType.LessOrEqual:
                        //"(field <= @conxxx)"
                        result = sc.Field + sql.LessOrEqual + prefix + valueStack.Count.ToString();
                        break;

                    case ComparisionType.Greater:
                        //"(field > @conxxx)"
                        result = sc.Field + sql.Greater + prefix + valueStack.Count.ToString();
                        break;

                    case ComparisionType.GreaterOrEqual:
                        //"(field >= @conxxx)"
                        result = sc.Field + sql.GreaterOrEqual + prefix + valueStack.Count.ToString();
                        break;
                }
                
                //append value to stack
                valueStack.Add(sc.Value);
            }//Single condition

            return result;
        }
        #endregion
        #endregion        

        #region Build Create Table clause
        /// <summary>
        /// Appends a Create Table sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="buffer">text buffer</param>
        protected virtual void BuildCreateTable(ObjectMap entity, StringBuilder buffer)
        {
            //"CREATE TABLE [Entity] ("
            buffer.Append(sql.CreateTable + sql.ItemStart + entity.DataEntity + sql.ItemEnd + " " + sql.GroupStart);

            //"[Column] type, [Column] type ..."
            BuildColumnNamesWithTypes(mMapper.GetAttributes(entity.ObjectEntity), buffer, "");

            //append primary constraints
            AttributeMap[] identifiers = mMapper.GetIdentifiers(entity.ObjectEntity);
            if (identifiers.Length > 0)
            {
                //", PRIMARY KEY ("
                buffer.Append(sql.ItemSeparator + sql.PrimaryKey + sql.GroupStart);
                //"[column], [column] ..."
                for (int i = 0; i < identifiers.Length; i++)
                {
                    if (i > 0) buffer.Append(sql.ItemSeparator);
                    buffer.Append(sql.ItemStart + identifiers[i].DataAttribute + sql.ItemEnd);
                }
                //")"
                buffer.Append(sql.GroupEnd);
            }
            //");"
            buffer.Append(sql.GroupEnd + sql.CommandEnd);
        }
        #endregion

        #region Build Delete Table clause
        /// <summary>
        /// Appends a Delete Table sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="buffer">text buffer</param>
        protected virtual void BuildDeleteTable(ObjectMap entity, StringBuilder buffer)
        {
            //"DROP TABLE [Entity];"
            buffer.Append(sql.DeleteTable + sql.ItemStart + entity.DataEntity + sql.ItemEnd + sql.CommandEnd);
        }
        #endregion

        #region Build Insert Record clause
        /// <summary>
        /// Appends an Insert record sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="attributes">Attribute mappings</param>
        /// <param name="buffer">text buffer</param>
        protected virtual void BuildInsertRecordClause(ObjectMap entity, AttributeMap[] attributes, StringBuilder buffer)
        {
            //"INSERT INTO [Entity]("
            buffer.Append(sql.InsertRecord + sql.ItemStart + entity.DataEntity + sql.ItemEnd + sql.GroupStart);

            //"[Column], [Column]..."
            BuildColumnNames(attributes, buffer, "", false);

            //") VALUES ("
            buffer.Append(sql.GroupEnd + sql.Values + sql.GroupStart);

            //"@column, @column..."
            int counter = 0;
            for (int i = 0; i < attributes.Length; i++)
            {
                AttributeMap map = attributes[i];
                if (!map.IsAutoGenerated)
                {
                    if (counter > 0) buffer.Append(sql.ItemSeparator);
                    buffer.Append(sql.ParameterStart + map.DataAttribute);
                    counter++;
                }
            }

            //");"
            buffer.Append(sql.GroupEnd + sql.CommandEnd);
        }
        #endregion

        #region Build Update Record clause
        /// <summary>
        /// Appends an Insert record sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="attributes">Attribute mappings</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="conditionFields">Condition attributes (usually the identifier attributes)</param>
        protected virtual void BuildUpdateRecordClause(ObjectMap entity, AttributeMap[] attributes, AttributeMap[] conditionFields, StringBuilder buffer)
        {
            //"UPDATE [Entity] SET "
            buffer.Append(sql.UpdateRecord + sql.ItemStart + entity.DataEntity + sql.ItemEnd + sql.SetValue);

            //"[column] = @column, [column] = @column..."
            bool first = true;
            for (int i = 0; i < attributes.Length; i++)
            {
                if (!attributes[i].IsIdentifier)
                {
                    if (first) first = false;
                    else buffer.Append(sql.ItemSeparator);
                    buffer.Append(sql.ItemStart + attributes[i].DataAttribute + sql.ItemEnd + sql.Equal + sql.ParameterStart + attributes[i].DataAttribute);
                }
            }

            //" WHERE "
            buffer.Append(sql.Condition);

            //[identifier] = @identifier, [identifier] = @identifier..."
            for (int i = 0; i < conditionFields.Length; i++)
            {
                if (i > 0) buffer.Append(sql.And);
                buffer.Append(sql.ItemStart + conditionFields[i].DataAttribute + sql.ItemEnd + sql.Equal + sql.ParameterStart + conditionFields[i].DataAttribute);
            }

            //";"
            buffer.Append(sql.CommandEnd);
        }

        /// <summary>
        /// Appends an Insert record sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="attributes">Attribute mappings</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="condition">Condition model</param>
        /// <param name="valueStack">holder for values during condition parsing</param>
        protected virtual void BuildUpdateRecordClause(ObjectMap entity, AttributeMap[] attributes, Condition condition, StringBuilder buffer, Thn.Collections.ArrayList valueStack)
        {
            //"UPDATE [Entity] SET "
            buffer.Append(sql.UpdateRecord + sql.ItemStart + entity.DataEntity + sql.ItemEnd + sql.SetValue);

            //"[column] = @column, [column] = @column..."
            bool first = true;
            for (int i = 0; i < attributes.Length; i++)
            {
                if (!attributes[i].IsIdentifier)
                {
                    if (first) first = false;
                    else buffer.Append(sql.ItemSeparator);
                    buffer.Append(sql.ItemStart + attributes[i].DataAttribute + sql.ItemEnd + sql.Equal + sql.ParameterStart + attributes[i].DataAttribute);
                }
            }

            //append condition
            BuildCondition(condition, buffer, valueStack);            

            //";"
            buffer.Append(sql.CommandEnd);
        }
        #endregion

        #region Build Delete Record clause
        /// <summary>
        /// Appends an Insert record sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="conditionFields">Condition attributes (usually the identifier attributes)</param>
        protected virtual void BuildDeleteRecordClause(ObjectMap entity, AttributeMap[] conditionFields, StringBuilder buffer)
        {
            //"DELETE FROM [Entity] WHERE "
            buffer.Append(sql.DeleteRecord + sql.ItemStart + entity.DataEntity + sql.ItemEnd + sql.Condition);            

            //WHERE [identifier] = @identifier, [identifier] = @identifier..."
            for (int i = 0; i < conditionFields.Length; i++)
            {
                if (i > 0) buffer.Append(sql.And);
                buffer.Append(sql.ItemStart + conditionFields[i].DataAttribute + sql.ItemEnd + sql.Equal + sql.ParameterStart + conditionFields[i].DataAttribute);
            }

            //";"
            buffer.Append(sql.CommandEnd);
        }

        /// <summary>
        /// Appends an Insert record sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="condition">Condition model</param>
        /// <param name="valueStack">holder for values during condition parsing</param>
        protected virtual void BuildDeleteRecordClause(ObjectMap entity, Condition condition, StringBuilder buffer, Thn.Collections.ArrayList valueStack)
        {
            //"DELETE FROM [Entity]"
            buffer.Append(sql.DeleteRecord + sql.ItemStart + entity.DataEntity + sql.ItemEnd);

            //append condition
            BuildCondition(condition, buffer, valueStack);

            //";"
            buffer.Append(sql.CommandEnd);
        }
        #endregion

        #region Build Select One Entity clause
        /// <summary>
        /// Appends a Select record sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="attributes">fields to be selected</param>
        /// <param name="criteria">condition model</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="valueStack">list to append values to while parsing condition</param>
        /// <param name="query">Optional query object</param>
        protected virtual void BuildSelectOneEntityClause(ObjectMap entity, AttributeMap[] attributes, Condition criteria, StringBuilder buffer, Thn.Collections.ArrayList valueStack, SelectQuery query)
        {
            //"SELECT "
            buffer.Append(sql.SelectRecord);

            //" DISTINCT "
            if ((query!=null) && (query.HasDistinct == true)) buffer.Append(sql.Distinct);

            //"[Column], [Column], ..."
            BuildColumnNames(attributes, buffer, "");

            //" FROM [entity]"
            buffer.Append(sql.FromTable + sql.ItemStart + entity.DataEntity + sql.ItemEnd);

            //" WHERE [column] = @conxxx, [column] = @conxxx ..."
            if (criteria != null) BuildCondition(criteria, buffer, valueStack);

            //" ORDER BY "
            if ((query != null) && (query.OrderByClause != null) && (query.OrderByClause.Count > 0))
            {
                buffer.Append(sql.OrderBy);

                for (int i = 0; i < query.OrderByClause.Count; i++)
                {
                    if (i > 0) buffer.Append(sql.ItemSeparator);
                    if (query.OrderByClause[i].Direction == Thn.Application.Sort.SortDirection.Ascending)
                    {
                        buffer.Append(query.OrderByClause[i].FieldName);
                    }
                    else
                    {
                        buffer.Append(query.OrderByClause[i].FieldName + sql.Descending);
                    }
                }
            }
        }
        #endregion
        #endregion

        #region Structuring
        #region Create structure for 1 object type
        /// <summary>
        /// Generate database structure specific to a single object type
        /// </summary>
        /// <param name="objectType">type of data object to generate</param>
        /// <param name="keepOldData">try to retain the existing data</param>
        public override void CreateStructure(Type objectType, bool keepOldData)
        {
            StringBuilder buffer = new StringBuilder();
            ObjectMap entity = Mapper.GetEntity(objectType);

            //drop current table
            BuildDeleteTable(entity, buffer);
            mSuppressException = true;
            ExecuteNonQuery(buffer.ToString(), true);
            mSuppressException = false;

            //create table
            buffer = new StringBuilder();
            BuildCreateTable(entity, buffer);            
            ExecuteNonQuery(buffer.ToString(), true);
        }
        #endregion

        #region Create all structures
        /// <summary>
        /// Generate database structures (table, stored procedure, etc.) of all registered object types
        /// </summary>
        /// <param name="keepOldData">try to retain the existing data</param>
        public override void CreateStructure(bool keepOldData)
        {
            Type[] types = RegisteredTypes;

            foreach (Type entity in types)
            {
                CreateStructure(entity, keepOldData);
            }
        }
        #endregion
        #endregion

        #region Convenient CRUD routines
        #region Insert
        #region Insert object(s)
        /// <summary>
        /// Add new object(s) into database
        /// </summary>
        public override void InsertObjects(params object[] values)
        {
            Type valueType = null;
            Type previousType = null;
            object value = null;
            IDbCommand command = null;
            AttributeMap[] attributes = null;
            ObjectMap entity = null;

            bool hasAuto = false;

            for (int i = 0; i < values.Length; i++)
            {
                value = values[i];
                valueType = value.GetType();

                //different object type from previous, prepare statement
                if (valueType != previousType)
                {
                    hasAuto = false;
                    previousType = valueType;
                    entity = mMapper.GetEntity(valueType);
                    attributes = mMapper.GetAttributes(valueType);
                    StringBuilder buffer = new StringBuilder();
                    BuildInsertRecordClause(entity, attributes, buffer);

                    //build command
                    if (command != null) command.Dispose();
                    command = mDialect.CreateCommand();
                    command.CommandText = buffer.ToString();

                    //build parameter
                    for (int m = 0; m < attributes.Length; m++)
                    {
                        AttributeMap map = attributes[m];
                        if (!map.IsAutoGenerated)
                        {
                            IDataParameter parameter = mDialect.CreateParameter(map.DataAttribute, map.ObjectAttributeType);
                            command.Parameters.Add(parameter);
                        }
                        else hasAuto = true;
                    }
                }

                //append values to parameters
                int paramIdx = 0;
                for (int m = 0; m < attributes.Length; m++)
                {
                    AttributeMap map = attributes[m];
                    if (!map.IsAutoGenerated)
                    {
                        IDataParameter parameter = (IDataParameter)command.Parameters[paramIdx];
                        parameter.Value = mMapper.GetObjectAttributeValue(value, map.ObjectAttribute);
                        paramIdx++;
                    }                    
                }

                //execute query
                OnInsertObject(value, command, entity, attributes, hasAuto);                
            }

            //clean up
            if (command != null) command.Dispose();
        }
        #endregion

        #region Insert collection
        /// <summary>
        /// Add a collection of objects to database
        /// </summary>
        public override void InsertObjects(IList values)
        {
            Type valueType = null;
            Type previousType = null;
            object value = null;
            IDbCommand command = null;
            AttributeMap[] attributes = null;
            ObjectMap entity = null;

            bool hasAuto = false;

            for (int i = 0; i < values.Count; i++)
            {
                value = values[i];
                valueType = value.GetType();

                //different object type from previous, prepare statement
                if (valueType != previousType)
                {
                    hasAuto = false;
                    previousType = valueType;
                    entity = mMapper.GetEntity(valueType);
                    attributes = mMapper.GetAttributes(valueType);
                    StringBuilder buffer = new StringBuilder();
                    BuildInsertRecordClause(entity, attributes, buffer);

                    //build command
                    if (command != null) command.Dispose();
                    command = mDialect.CreateCommand();
                    command.CommandText = buffer.ToString();

                    //build parameter
                    for (int m = 0; m < attributes.Length; m++)
                    {
                        AttributeMap map = attributes[m];
                        if (!map.IsAutoGenerated)
                        {
                            IDataParameter parameter = mDialect.CreateParameter(map.DataAttribute, map.ObjectAttributeType);
                            command.Parameters.Add(parameter);
                        }
                        else hasAuto = true;
                    }
                }

                //append values to parameters
                int paramIdx = 0;
                for (int m = 0; m < attributes.Length; m++)
                {
                    AttributeMap map = attributes[m];
                    if (!map.IsAutoGenerated)
                    {
                        IDataParameter parameter = (IDataParameter)command.Parameters[paramIdx];
                        object attributeValue = mMapper.GetObjectAttributeValue(value, map.ObjectAttribute);
                        parameter.Value = attributeValue != null ? attributeValue : DBNull.Value;
                        paramIdx++;
                    }
                }

                //execute query
                OnInsertObject(value, command, entity, attributes, hasAuto);                
            }

            //clean up
            if (command != null) command.Dispose();
        }
        #endregion

        #region On Insert Object
        /// <summary>
        /// Raised after insert command has been built, this method will execute the command.
        /// <para>Override this method for acquiring auto-generated IDs, etc.</para>
        /// </summary>
        /// <param name="value">the data object to be inserted</param>
        /// <param name="command">the prepared database command</param>
        /// <param name="entity">mapping of object to insert</param>
        /// <param name="attributes">mapping of attributes</param>
        /// <param name="hasAuto">Whether identifier of object is auto-generated field</param>
        protected virtual void OnInsertObject(object value, IDbCommand command, ObjectMap entity, AttributeMap[] attributes, bool hasAuto)
        {
            ExecuteNonQuery(command, false);
        }
        #endregion

        #endregion

        #region Select
        /// <summary>
        /// Get object(s) matching the condition
        /// </summary>
        /// <param name="objectType">Type of object to query against</param>
        /// <param name="condition">Query criteria. If null, select all objects</param>
        /// <returns>A collection is always returned. If there are no object matches the condition, the collection is empty</returns>
        public override IList SelectObjects(Type objectType, Condition condition)
        {
            IList result = null;
            Thn.Collections.ArrayList valueStack = new Thn.Collections.ArrayList();
            StringBuilder buffer = new StringBuilder();
            ObjectMap entity = mMapper.GetEntity(objectType);
            AttributeMap[] attributes = mMapper.GetAttributes(objectType);

            //construct command
            BuildSelectOneEntityClause(entity, attributes, condition, buffer, valueStack, null);
            IDbCommand command = mDialect.CreateCommand();
            command.CommandText = buffer.ToString();

            //append value to condition 
            if (condition != null)
            {
                for (int i = 0; i < valueStack.Count; i++)
                {
                    IDataParameter parameter = mDialect.CreateParameter("@con" + i.ToString(), valueStack[i].GetType());
                    parameter.Value = valueStack[i];
                    command.Parameters.Add(parameter);
                }
            }

            //execute query
            result = ExecuteReaderOneEntity(entity, attributes, command, true);

            return result;
        }
        #endregion

        #region Update
        #region Update object(s)
        /// <summary>
        /// Update object(s) into database
        /// </summary>
        public override void UpdateObjects(params object[] values)
        {
            Type valueType = null;
            Type previousType = null;
            object value = null;
            IDbCommand command = null;
            AttributeMap[] attributes = null;
            AttributeMap[] identifiers = null;
            ObjectMap entity = null;

            for (int i = 0; i < values.Length; i++)
            {
                value = values[i];
                valueType = value.GetType();

                //different object type from previous, prepare statement
                if (valueType != previousType)
                {
                    previousType = valueType;
                    entity = mMapper.GetEntity(valueType);
                    attributes = mMapper.GetAttributes(valueType);
                    identifiers = mMapper.GetIdentifiers(valueType);
                    if (identifiers.Length <= 0)
                    {//throw error if can't find any identifier                        
                        NoIdentifierException.Publish(valueType);
                        break;
                    }

                    StringBuilder buffer = new StringBuilder();
                    BuildUpdateRecordClause(entity, attributes, identifiers, buffer);

                    //build command
                    if (command != null) command.Dispose();
                    command = mDialect.CreateCommand();
                    command.CommandText = buffer.ToString();

                    #region Build parameters
                    /* WORK AROUND DUE TO BUG IN OLEDB. SOME DATABASE LIKE ACCESS DOES NOT KNOW NAMED PARAMETERS */
                    //build parameter for normal attributes
                    for (int m = 0; m < attributes.Length; m++)
                    {
                        if (!attributes[m].IsIdentifier)
                        {
                            IDataParameter parameter = mDialect.CreateParameter(attributes[m].DataAttribute, attributes[m].ObjectAttributeType);
                            command.Parameters.Add(parameter);
                        }
                    }
                    
                    //build parameter for identifier attributes
                    for (int m = 0; m < identifiers.Length; m++)
                    {
                        IDataParameter parameter = mDialect.CreateParameter(identifiers[m].DataAttribute, identifiers[m].ObjectAttributeType);
                        command.Parameters.Add(parameter);
                    }
                    #endregion
                }

                #region Append values to parameters
                /* WORK AROUND DUE TO BUG IN OLEDB. SOME DATABASE LIKE ACCESS DOES NOT KNOW NAMED PARAMETERS */
                //append values to normal parameters
                int idx = 0;
                for (int m = 0; m < attributes.Length; m++)
                {
                    if (!attributes[m].IsIdentifier)
                    {
                        IDataParameter parameter = (IDataParameter)command.Parameters[idx];
                        //parameter.Value = mMapper.GetObjectAttributeValue(value, attributes[m].ObjectAttribute);
                        object attributeValue = mMapper.GetObjectAttributeValue(value, attributes[m].ObjectAttribute);
                        parameter.Value = attributeValue != null ? attributeValue : DBNull.Value;
                        idx++;
                    }
                }

                //append values to identifier parameters
                for (int m = 0; m < identifiers.Length; m++)
                {
                    IDataParameter parameter = (IDataParameter)command.Parameters[idx];
                    //parameter.Value = mMapper.GetObjectAttributeValue(value, identifiers[m].ObjectAttribute);
                    object attributeValue = mMapper.GetObjectAttributeValue(value, identifiers[m].ObjectAttribute);
                    parameter.Value = attributeValue != null ? attributeValue : DBNull.Value;
                    idx++;
                }
                #endregion

                //execute query
                ExecuteNonQuery(command, false);
            }

            //clean up
            if (command != null) command.Dispose();
        }
        #endregion

        #region Update collection
        /// <summary>
        /// Update a collection of objects to database
        /// </summary>
        public override void UpdateObjects(IList values)
        {
            Type valueType = null;
            Type previousType = null;
            object value = null;
            IDbCommand command = null;
            AttributeMap[] attributes = null;
            AttributeMap[] identifiers = null;
            ObjectMap entity = null;

            for (int i = 0; i < values.Count; i++)
            {
                value = values[i];
                valueType = value.GetType();

                //different object type from previous, prepare statement
                if (valueType != previousType)
                {
                    previousType = valueType;
                    entity = mMapper.GetEntity(valueType);
                    attributes = mMapper.GetAttributes(valueType);
                    identifiers = mMapper.GetIdentifiers(valueType);
                    if (identifiers.Length <= 0)
                    {//throw error if can't find any identifier
                        NoIdentifierException.Publish(valueType);
                        break;
                    }

                    StringBuilder buffer = new StringBuilder();
                    BuildUpdateRecordClause(entity, attributes, identifiers, buffer);

                    //build command
                    if (command != null) command.Dispose();
                    command = mDialect.CreateCommand();
                    command.CommandText = buffer.ToString();

                    #region Build parameters
                    /* WORK AROUND DUE TO BUG IN OLEDB. SOME DATABASE LIKE ACCESS DOES NOT KNOW NAMED PARAMETERS */
                    //build parameter for normal attributes
                    for (int m = 0; m < attributes.Length; m++)
                    {
                        if (!attributes[m].IsIdentifier)
                        {
                            IDataParameter parameter = mDialect.CreateParameter(attributes[m].DataAttribute, attributes[m].ObjectAttributeType);
                            command.Parameters.Add(parameter);
                        }
                    }

                    //build parameter for identifier attributes
                    for (int m = 0; m < identifiers.Length; m++)
                    {
                        IDataParameter parameter = mDialect.CreateParameter(identifiers[m].DataAttribute, identifiers[m].ObjectAttributeType);
                        command.Parameters.Add(parameter);
                    }
                    #endregion
                }

                #region Append values to parameters
                /* WORK AROUND DUE TO BUG IN OLEDB. SOME DATABASE LIKE ACCESS DOES NOT KNOW NAMED PARAMETERS */
                //append values to normal parameters
                int idx = 0;
                for (int m = 0; m < attributes.Length; m++)
                {
                    if (!attributes[m].IsIdentifier)
                    {
                        IDataParameter parameter = (IDataParameter)command.Parameters[idx];
                        //parameter.Value = mMapper.GetObjectAttributeValue(value, attributes[m].ObjectAttribute);
                        object attributeValue = mMapper.GetObjectAttributeValue(value, attributes[m].ObjectAttribute);
                        parameter.Value = attributeValue != null ? attributeValue : DBNull.Value;
                        idx++;
                    }
                }

                //append values to identifier parameters
                for (int m = 0; m < identifiers.Length; m++)
                {
                    IDataParameter parameter = (IDataParameter)command.Parameters[idx];
                    object attributeValue = mMapper.GetObjectAttributeValue(value, identifiers[m].ObjectAttribute);
                    parameter.Value = attributeValue != null ? attributeValue : DBNull.Value;
                    idx++;
                }
                #endregion

                //execute query
                ExecuteNonQuery(command, false);
            }

            //clean up
            if (command != null) command.Dispose();
        }
        #endregion
        #endregion

        #region Delete
        /// <summary>
        /// Delete object(s) from database using their identifiers
        /// </summary>
        /// <param name="values">objects to delete</param>
        public override void DeleteObjects(params object[] values)
        {
            Type valueType = null;
            Type previousType = null;
            object value = null;
            IDbCommand command = null;
            AttributeMap[] identifiers = null;
            ObjectMap entity = null;

            for (int i = 0; i < values.Length; i++)
            {
                value = values[i];
                valueType = value.GetType();

                //different object type from previous, prepare statement
                if (valueType != previousType)
                {
                    previousType = valueType;
                    entity = mMapper.GetEntity(valueType);
                    identifiers = mMapper.GetIdentifiers(valueType);
                    if (identifiers.Length <= 0)
                    {//throw error if can't find any identifier
                        //DataExceptionBase exception = DataExceptionBase.Create(DataExceptionConstants.NoIdentifier, typeof(DataExceptionBase), null, valueType);
                        //if (DataTierSettings.AutoPublishError) Log.Error(exception);
                        //if (!(DataTierSettings.SilentError)) throw exception;
                        NoIdentifierException.Publish(valueType);
                        break;
                    }

                    StringBuilder buffer = new StringBuilder();
                    BuildDeleteRecordClause(entity, identifiers, buffer);

                    //build command
                    if (command != null) command.Dispose();
                    command = mDialect.CreateCommand();
                    command.CommandText = buffer.ToString();

                    //build parameter
                    for (int m = 0; m < identifiers.Length; m++)
                    {
                        IDataParameter parameter = mDialect.CreateParameter(identifiers[m].DataAttribute, identifiers[m].ObjectAttributeType);
                        command.Parameters.Add(parameter);
                    }
                }

                //append values to parameters
                for (int m = 0; m < identifiers.Length; m++)
                {
                    IDataParameter parameter = (IDataParameter)command.Parameters[m];
                    parameter.Value = mMapper.GetObjectAttributeValue(value, identifiers[m].ObjectAttribute);
                }

                //execute query
                ExecuteNonQuery(command, false);
            }

            //clean up
            if (command != null) command.Dispose();
        }

        /// <summary>
        /// Delete object(s) from database using their identifiers
        /// </summary>
        /// <param name="values">objects to delete</param>
        public override void DeleteObjects(IList values)
        {
            Type valueType = null;
            Type previousType = null;
            object value = null;
            IDbCommand command = null;
            AttributeMap[] identifiers = null;
            ObjectMap entity = null;

            for (int i = 0; i < values.Count; i++)
            {
                value = values[i];
                valueType = value.GetType();

                //different object type from previous, prepare statement
                if (valueType != previousType)
                {
                    previousType = valueType;
                    entity = mMapper.GetEntity(valueType);
                    identifiers = mMapper.GetIdentifiers(valueType);
                    if (identifiers.Length <= 0)
                    {//throw error if can't find any identifier
                        NoIdentifierException.Publish(valueType);
                        break;
                    }

                    StringBuilder buffer = new StringBuilder();
                    BuildDeleteRecordClause(entity, identifiers, buffer);

                    //build command
                    if (command != null) command.Dispose();
                    command = mDialect.CreateCommand();
                    command.CommandText = buffer.ToString();

                    //build parameter
                    for (int m = 0; m < identifiers.Length; m++)
                    {
                        IDataParameter parameter = mDialect.CreateParameter(identifiers[m].DataAttribute, identifiers[m].ObjectAttributeType);
                        command.Parameters.Add(parameter);
                    }
                }

                //append values to parameters
                for (int m = 0; m < identifiers.Length; m++)
                {
                    IDataParameter parameter = (IDataParameter)command.Parameters[m];
                    parameter.Value = mMapper.GetObjectAttributeValue(value, identifiers[m].ObjectAttribute);
                }

                //execute query
                ExecuteNonQuery(command, false);
            }

            //clean up
            if (command != null) command.Dispose();
        }        
        #endregion
        #endregion

        #region Transaction
        /// <summary>
        /// Transaction currently in effect
        /// </summary>
        protected IDbTransaction mCurrentTransaction = null;
        /// <summary>
        /// Context of current transaction
        /// </summary>
        protected object mCurrentTransactionContext = null;

        #region Begin Transaction
        /// <summary>
        /// Start a new transaction.
        /// </summary>
        /// <returns>The transaction context specific to database</returns>
        public override object BeginTransaction()
        {
            object context = Guid.NewGuid();

            if (mCurrentTransaction == null)
            {//no transaction pending yet, start a new one
                PrepareConnection();
                mCurrentTransaction = Connection.BeginTransaction();
                mCurrentTransactionContext = context;
            }

            return context;
        }
        #endregion

        #region Commit Transaction
        /// <summary>
        /// Execute task(s) contained in this transaction and persist changes to database
        /// </summary>
        /// <param name="transactionContext">The transaction context specific to database</param>
        public override void CommitTransaction(object transactionContext)
        {
            if (mCurrentTransactionContext == transactionContext)
            {//context matches the very first transaction
                if (mCurrentTransaction != null) mCurrentTransaction.Commit();
                mCurrentTransaction = null;
                mCurrentTransactionContext = null;
            }

            //close connection if neccessary
            CleanupConnection();
        }
        #endregion

        #region Cancel Transaction
        /// <summary>
        /// Abandon changes made within this transaction
        /// </summary>
        /// <param name="transactionContext">The transaction context specific to database</param>
        public override void CancelTransaction(object transactionContext)
        {
            if (mCurrentTransactionContext == transactionContext)
            {//context matches the very first transaction
                if (mCurrentTransaction != null) mCurrentTransaction.Rollback();
                mCurrentTransaction = null;
                mCurrentTransactionContext = null;
            }

            //close connection if neccessary
            CleanupConnection();
        }
        #endregion
        #endregion

        #region Execute query command
        #region Execute base command
        /// <summary>
        /// Proccess a query command
        /// </summary>
        /// <param name="command">The command to process</param>
        public override ExecuteResult Execute(QueryBase command)
        {
            ExecuteResult result = null;            

            if (command is InsertQuery) Execute((InsertQuery)command);
            else if (command is UpdateQuery) Execute((UpdateQuery)command);
            else if (command is DeleteQuery) Execute((DeleteQuery)command);
            else if (command is SelectQuery)
            {
                result = Execute((SelectQuery)command);
            }
            else if (command is TransactionQuery)
            {
                result = Execute((TransactionQuery)command);
            }

            return result;
        }
        #endregion

        #region Execute Insert query
        /// <summary>
        /// Process an insert query command
        /// </summary>
        /// <param name="command">The command to process</param>
        protected virtual void Execute(InsertQuery command)
        {
            //pre-condition
            object obj = command.InsertClause;
            if (command.InsertClause == null)
            {
                if (!mSuppressException)
                {
                    //DataExceptionBase error = DataExceptionBase.Create(DataExceptionConstants.NoValueInQuery, typeof(QuerySyntaxException), null, null);
                    //if (DataTierSettings.AutoPublishError) Log.Error(error);
                    //if (!DataTierSettings.SilentError) throw error;
                    NoValueInQueryException.Publish();                    
                }
            }
            else InsertObjects((IList)obj);
        }
        #endregion

        #region Execute Update query
        /// <summary>
        /// Process an update query command
        /// </summary>
        /// <param name="command">The command to process</param>
        protected virtual void Execute(UpdateQuery command)
        {
            //pre-condition
            if (command.UpdateClause == null)
            {
                if (!mSuppressException)
                {
                    //DataExceptionBase error = DataExceptionBase.Create(DataExceptionConstants.NoValueInQuery, typeof(QuerySyntaxException), null, null);
                    //if (DataTierSettings.AutoPublishError) Log.Error(error);
                    //if (!DataTierSettings.SilentError) throw error;
                    NoValueInQueryException.Publish();
                }
            }

            IList values = (IList)command.UpdateClause;

            if ((command.FieldClause == null) && (command.WhereClause == null))
            {//Fields not specified
                UpdateObjects(values);
            }
            else
            {
                Type valueType = null;
                Type previousType = null;
                object value = null;
                IDbCommand cmd = null;
                AttributeMap[] attributes = null;
                AttributeMap[] identifiers = null;
                ObjectMap entity = null;
                StringBuilder buffer = new StringBuilder();
                Thn.Collections.ArrayList valueStack = new Thn.Collections.ArrayList();

                for (int i = 0; i < values.Count; i++)
                {
                    value = values[i];
                    valueType = value.GetType();

                    //different object type from previous, prepare statement
                    if (valueType != previousType)
                    {
                        previousType = valueType;
                        entity = mMapper.GetEntity(valueType);

                        //prepare attributes
                        #region Prepare attributes
                        if (command.FieldClause == null) attributes = mMapper.GetAttributes(valueType);
                        else
                        {
                            Thn.Collections.ArrayList attributeList = new Thn.Collections.ArrayList();
                            for (int m = 0; m < command.FieldClause.Count; m++)
                            {
                                AttributeMap am = mMapper.GetAttribute(valueType, command.FieldClause[m].ToString());
                                if (am != null) attributeList.Add(am);
                                else
                                {
                                    if (!mSuppressException)
                                    {
                                        //DataExceptionBase error = DataExceptionBase.Create(DataExceptionConstants.NoValueInQuery, typeof(QuerySyntaxException), null, valueType, command.FieldClause[m].ToString());
                                        //if (DataTierSettings.AutoPublishError) Log.Error(error);
                                        //if (!DataTierSettings.SilentError) throw error;
                                        NoValueInQueryException.Publish();
                                    }
                                }
                            }
                            attributes = new AttributeMap[attributeList.Count];
                            attributeList.CopyTo(attributes);
                        }//prepare attribute
                        #endregion

                        //prepare condition
                        #region Prepare condition
                        if (command.WhereClause == null)
                        {
                            //no condition specified, use the identifiers of entity
                            identifiers = mMapper.GetIdentifiers(valueType);
                            if (identifiers.Length <= 0)
                            {//throw error if can't find any identifier
                                //DataExceptionBase exception = DataExceptionBase.Create(DataExceptionConstants.NoIdentifier, typeof(DataExceptionBase), null, valueType);
                                //if (DataTierSettings.AutoPublishError) Log.Error(exception);
                                //if (!(DataTierSettings.SilentError)) throw exception;
                                NoIdentifierException.Publish(valueType);
                                break;
                            }

                            BuildUpdateRecordClause(entity, attributes, identifiers, buffer);

                            //build command
                            if (cmd != null) cmd.Dispose();
                            cmd = mDialect.CreateCommand();
                            cmd.CommandText = buffer.ToString();

                            #region Build parameters
                            /* WORK AROUND DUE TO BUG IN OLEDB. SOME DATABASE LIKE ACCESS DOES NOT KNOW NAMED PARAMETERS */
                            //build parameter for normal attributes
                            for (int m = 0; m < attributes.Length; m++)
                            {
                                if (!attributes[m].IsIdentifier)
                                {
                                    IDataParameter parameter = mDialect.CreateParameter(attributes[m].DataAttribute, attributes[m].ObjectAttributeType);
                                    cmd.Parameters.Add(parameter);
                                }
                            }

                            //build parameter for identifier attributes
                            for (int m = 0; m < identifiers.Length; m++)
                            {
                                IDataParameter parameter = mDialect.CreateParameter(identifiers[m].DataAttribute, identifiers[m].ObjectAttributeType);
                                cmd.Parameters.Add(parameter);
                            }
                            #endregion
                        }//WhereClause == null
                        else
                        {
                            BuildUpdateRecordClause(entity, attributes, command.WhereClause, buffer, valueStack);

                            //build command
                            if (cmd != null) cmd.Dispose();
                            cmd = mDialect.CreateCommand();
                            cmd.CommandText = buffer.ToString();

                            #region Build parameters
                            //build parameter for normal attributes
                            for (int m = 0; m < attributes.Length; m++)
                            {
                                if (!attributes[m].IsIdentifier)
                                {
                                    IDataParameter parameter = mDialect.CreateParameter(attributes[m].DataAttribute, attributes[m].ObjectAttributeType);
                                    cmd.Parameters.Add(parameter);
                                }
                            }

                            //build parameter for conditions
                            for (int m = 0; m < valueStack.Count; m++)
                            {
                                IDataParameter parameter = mDialect.CreateParameter("con" + m.ToString(), valueStack[i].GetType());
                                cmd.Parameters.Add(parameter);
                            }
                            #endregion
                        }//WhereClause != null
                        #endregion
                    }//valueType != previousType

                    #region Append values to parameters

                    int idx = 0;
                    if (command.WhereClause == null)
                    {
                        /* WORK AROUND DUE TO BUG IN OLEDB. SOME DATABASE LIKE ACCESS DOES NOT KNOW NAMED PARAMETERS */
                        //append values to normal parameters
                        for (int m = 0; m < attributes.Length; m++)
                        {
                            if (!attributes[m].IsIdentifier)
                            {
                                IDataParameter parameter = (IDataParameter)cmd.Parameters[idx];
                                parameter.Value = mMapper.GetObjectAttributeValue(value, attributes[m].ObjectAttribute);
                                idx++;
                            }
                        }

                        //append values to identifier parameters
                        for (int m = 0; m < identifiers.Length; m++)
                        {
                            IDataParameter parameter = (IDataParameter)cmd.Parameters[idx];

                            parameter.Value = mMapper.GetObjectAttributeValue(value, identifiers[m].ObjectAttribute);
                            idx++;
                        }
                    }
                    else
                    {//WhereClause != null
                        //append values to normal parameters
                        for (int m = 0; m < attributes.Length; m++)
                        {
                            IDataParameter parameter = (IDataParameter)cmd.Parameters[idx];
                            parameter.Value = mMapper.GetObjectAttributeValue(value, attributes[m].ObjectAttribute);
                            idx++;
                        }

                        //append values to conditional parameters
                        for (int m = 0; m < valueStack.Count; m++)
                        {
                            IDataParameter parameter = (IDataParameter)cmd.Parameters[idx];

                            parameter.Value = valueStack[m];
                            idx++;
                        }
                    }//WhereClause != null

                    #endregion

                    //execute query
                    ExecuteNonQuery(cmd, false);
                }

                //clean up
                if (cmd != null) cmd.Dispose();
            }//Fields or Where specified
        }
        #endregion

        #region Execute Delete query
        /// <summary>
        /// Process a delete query command
        /// </summary>
        /// <param name="command">The command to process</param>
        protected virtual void Execute(DeleteQuery command)
        {
            //pre-condition            
            if ((command.DeleteClause == null) && (command.FromClause == null))
            {
                if (!mSuppressException)
                {
                    //DataExceptionBase error = DataExceptionBase.Create(DataExceptionConstants.NoValueInQuery, typeof(QuerySyntaxException), null, null);
                    //if (DataTierSettings.AutoPublishError) Log.Error(error);
                    //if (!DataTierSettings.SilentError) throw error;
                    NoValueInQueryException.Publish();
                }
            }

            //If objects to be delete has been specified, delete them by identifiers
            if (command.DeleteClause != null)
            {
                //objects to delete has been specified
                IList values = (IList)command.DeleteClause;

                DeleteObjects(values);
            }
            else
            {//Objeccts to be delete has not been specified, delete them by condition
                ObjectMap entity = mMapper.GetEntity((Type)command.FromClause);
                StringBuilder buffer = new StringBuilder();
                Thn.Collections.ArrayList valueStack = new Thn.Collections.ArrayList();

                //build query
                BuildDeleteRecordClause(entity, command.WhereClause, buffer, valueStack);

                //build command
                IDbCommand cmd = mDialect.CreateCommand();                
                cmd.CommandText = buffer.ToString();

                //append parameter
                for (int m = 0; m < valueStack.Count; m++)
                {
                    IDataParameter parameter = mDialect.CreateParameter("@con" + m.ToString(), valueStack[m].GetType());
                    parameter.Value = valueStack[m];
                    cmd.Parameters.Add(parameter);
                }

                //execute query
                ExecuteNonQuery(cmd, true);
            }

            
        }
        #endregion

        #region Execute Select query
        #region Select Single Entity
        /// <summary>
        /// Process select query command for one single entity
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="command">query command</param>
        /// <returns>List of result. If no records returned, list is empty</returns>
        protected virtual ExecuteResult SelectSingleEntity(ObjectMap entity, SelectQuery command)
        {
            Thn.Collections.ArrayList valueStack = new Thn.Collections.ArrayList();
            StringBuilder buffer = new StringBuilder();
            AttributeMap[] attributes = null;

            //get attributes
            if (command.FieldClause == null)
            {
                //get all attributes
                attributes = mMapper.GetAttributes(entity.ObjectEntity);
            }
            else
            {
                attributes = new AttributeMap[command.FieldClause.Count];

                //select only specified attributes
                for (int i = 0; i < command.FieldClause.Count; i++)
                {
                    attributes[i] = mMapper.GetAttribute(entity.ObjectEntity, command.FieldClause[i].ToString());                    
                }
            }

            //build command
            IDbCommand cmd = mDialect.CreateCommand();            
            BuildSelectOneEntityClause(entity, attributes, command.WhereClause, buffer, valueStack, command);
            cmd.CommandText = buffer.ToString();

            //append criteria parameters
            for (int i = 0; i < valueStack.Count; i++)
            {
                IDataParameter parameter = mDialect.CreateParameter(sql.ParameterStart + "con" + i.ToString(), valueStack[i].GetType());
                //IDataParameter parameter = mDialect.CreateParameter("con" + i.ToString(), valueStack[i].GetType());
                parameter.Value = valueStack[i];
                cmd.Parameters.Add(parameter);
            }

            IList result = ExecuteReaderOneEntity(entity, attributes, cmd, true);

            return new ExecuteResult(result);
        }
        #endregion

        #region Select Multi-entity
        /// <summary>
        /// Procecss select query for multi-entity query
        /// </summary>
        /// <param name="entities">entities to be queried</param>
        /// <param name="command">query command</param>
        /// <returns>TBD</returns>
        protected virtual ExecuteResult SelectMultiEntity(ObjectMap[] entities, SelectQuery command)
        {
            throw new NotSupportedException("Sorry multi-table selection is not yet supported");
        }
        #endregion

        #region Execute Select
        /// <summary>
        /// Process a select query command
        /// </summary>
        /// <param name="command">The command to process</param>
        protected virtual ExecuteResult Execute(SelectQuery command)
        {
            ExecuteResult result = null;

            //pre-condition
            if (command.FromClause==null)
            {
                if (!mSuppressException)
                {
                    //DataExceptionBase error = DataExceptionBase.Create(DataExceptionConstants.EntityNotSpecified, typeof(QuerySyntaxException), null, null);
                    //if (DataTierSettings.AutoPublishError) Log.Error(error);
                    //if (!DataTierSettings.SilentError) throw error;
                    QuerySyntaxException.Publish(DataExceptionConstants.EntityNotSpecified);
                }
            }

            //get entities
            ObjectMap[] entities = new ObjectMap[command.FromClause.Count];
            for (int i = 0; i < command.FromClause.Count; i++)
            {
                entities[i] = mMapper.GetEntity((Type)command.FromClause[i]);
            }

            //process query
            if (entities.Length == 1) result = SelectSingleEntity(entities[0], command);
            else result = SelectMultiEntity(entities, command);

            return result;
        }
        #endregion
        #endregion

        #region Execute Transaction query
        /// <summary>
        /// Process a transaction query command
        /// </summary>
        /// <param name="command">The command to process</param>
        protected virtual ExecuteResult Execute(TransactionQuery command)
        {
            ExecuteResult result = null;

            object scope = BeginTransaction();

            QueryCollection queries = command.Transaction.Commands;

            //make sure that error is always thrown so that I can handle transaction consistently
            bool silent = DataTierSettings.SilentError;
            DataTierSettings.SilentError = false;

            try
            {
                for (int i = 0; i < queries.Count; i++)
                {
                    ExecuteResult newResult = Execute(queries[i]);
                    result = ProcessResult(result, newResult);
                }

                //If I can go this far without exceptions, I can commit now
                CommitTransaction(scope);

                //reverse the state of silent settings
                if (silent) DataTierSettings.SilentError = silent;
            }
            catch (Exception error)
            {
                //uh oh, exception occured, I'll have to rollback the transaction
                CancelTransaction(scope);

                //reverse the state of silent settings
                if (silent) DataTierSettings.SilentError = silent;

                //process error
                if (!mSuppressException)
                {
                    DataExceptionBase exception = mDialect.ParseException(error);

                    if (DataTierSettings.AutoPublishError) Log.Error(exception);
                    if (!silent) throw exception;
                }
            }

            return result;
        }
        #endregion
        #endregion

        #region Process result
        /// <summary>
        /// Combine results of different query.
        /// At this time, this function is not complete, it will always return the new result (if not null)
        /// </summary>
        /// <param name="oldResult">current result set</param>
        /// <param name="newResult">new result set</param>
        /// <returns></returns>
        protected virtual ExecuteResult ProcessResult(ExecuteResult oldResult, ExecuteResult newResult)
        {
            ExecuteResult result = oldResult;

            if (newResult != null) result = newResult;

            return result;
        }
        #endregion
    }
}
